﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;
using System.Configuration;

namespace SWEN3Application.Classes
{
    class InvoiceDBManager
    {
        public static int GenerateInvoice(Invoice details)
        {
            SqlCeConnection conn = null;
            int InvoiceId = 0;
            try
            {
                conn = new SqlCeConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SWEN3ConnectionString"].ConnectionString;
                conn.Open();
                SqlCeCommand comm = new SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Invoice(BookingNo,RoomNo,CheckIn,CheckOut,RoomType,RoomRates,CurrencyRates,CreditCardNo,SecurityCode,CardType,ExpiryDate)" +
                    " VALUES (@BookingNo,@RoomNo,@CheckIn,@CheckOut,@RoomType,@RoomRates,@CurrencyRates,@CreditCardNo,@SecurityCode,@CardType,@ExpiryDate)";
                comm.Parameters.AddWithValue("@BookingNo", details.BookingNo);
                comm.Parameters.AddWithValue("@RoomNo", details.RoomNo);
                comm.Parameters.AddWithValue("@CheckIn", details.CheckIn);
                comm.Parameters.AddWithValue("@CheckOut", details.CheckOut);
                comm.Parameters.AddWithValue("@RoomType", details.RoomType);
                comm.Parameters.AddWithValue("@RoomRates", details.RoomRates);
                comm.Parameters.AddWithValue("@CurrencyRates", details.CurrencyRates);
                comm.Parameters.AddWithValue("@CreditCardNo", details.CreditCardNo);
                comm.Parameters.AddWithValue("@SecurityCode", details.SecurityCode);
                comm.Parameters.AddWithValue("@CardType", details.CardType);
                if (details.ExpiryDate == DateTime.MinValue)
                {
                    comm.Parameters.AddWithValue("@ExpiryDate", DBNull.Value);
                }
                else
                {
                    comm.Parameters.AddWithValue("@ExpiryDate", details.ExpiryDate);
                }
                if (comm.ExecuteNonQuery() > 0)
                {
                    SqlCeCommand comm2 = new SqlCeCommand();
                    comm2.Connection = conn;
                    comm2.CommandText = "SELECT * FROM Invoice WHERE InvoiceId IN (SELECT MAX(InvoiceId) FROM Invoice)";
                    comm2.ExecuteNonQuery();
                    SqlCeDataReader dr = comm2.ExecuteReader();
                    if (dr.Read())
                    {
                        InvoiceId = (int)dr["InvoiceId"];
                    }
                    dr.Close();
                }
            }
            catch (Exception e)
            {
                return 0;
            }
            finally
            {
                conn.Close();
            }
            return InvoiceId;
        }

        public static Invoice GetInvoiceByBookingNo(int BookingNo)
        {
            SqlCeConnection conn = null;
            Invoice i = null;
            try
            {
                conn = new SqlCeConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SWEN3ConnectionString"].ConnectionString;
                conn.Open();
                SqlCeCommand comm = new SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Invoice WHERE BookingNo = @BookingNo";
                comm.Parameters.AddWithValue("@BookingNo", BookingNo);
                SqlCeDataReader dr = comm.ExecuteReader();
                DateTime Default = DateTime.Parse("1/1/2001");
                if (dr.Read())
                {
                    i = new Invoice();
                    i.InvoiceId = (int)dr["InvoiceId"];
                    i.BookingNo = (int)dr["BookingNo"];
                    i.RoomNo = (int)dr["RoomNo"];
                    i.CheckIn = (DateTime)dr["CheckIn"];
                    i.CheckOut = (DateTime)dr["CheckOut"];
                    i.RoomType = (String)(dr["RoomType"]);
                    i.RoomRates = (Double)(dr["RoomRates"]);
                    i.CurrencyRates = (Double)(dr["CurrencyRates"]);
                    i.CreditCardNo = (String)(dr["CreditCardNo"]);
                    if (dr["SecurityCode"].ToString() == "0")
                    {
                        i.SecurityCode = 0;
                    }
                    else
                    {
                        i.SecurityCode = (int)(dr["SecurityCode"]);
                    }
                    i.CardType = (String)(dr["CardType"]);
                    i.ExpiryDate = (DateTime)(dr["ExpiryDate"] != DBNull.Value ? dr["ExpiryDate"] : Default);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlCeException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return i;
        }

        public static int DeleteBookingByInvoiceId(int InvoiceId)
        {
            int rowsdeleted = 0;

            SqlCeConnection conn = null;
            try
            {
                conn = new SqlCeConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SWEN3ConnectionString"].ConnectionString;
                conn.Open();
                SqlCeCommand comm = new SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "Delete from Invoice where InvoiceId=@InvoiceId";
                comm.Parameters.AddWithValue("@InvoiceId", InvoiceId);
                rowsdeleted = comm.ExecuteNonQuery();
            }
            catch (SqlCeException e)
            {
                throw e;
            }
            return rowsdeleted;
        }
    }
}
